package com.pets.springboot_petshop.mapper;

import com.pets.springboot_petshop.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @Author: wxk
 * @Email: 2089967794@qq.com
 * @Date: 2024/10/14 下午4:03 周一
 * @Description: 用户服务预约数据访问层接口
 */
@Mapper
@Repository
public interface ServiceappointmentMapper {
    //id查询
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "a.total_amount, " +
            "a.discount, " +
            "c.id as service_id, " +
            "c.name, " +
            "c.img, " +
            "c.original_price, " +
            "b.email, " +
            "b.phone, " +
            "c.discount_price, " +
            "a.ordernumber, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "a.appointmentslot_id, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "WHERE a.id = #{id}")
    Serviceappointment select(int id);

    //年销售
    @Select("WITH RECURSIVE MonthSequence AS (" +
            "  SELECT 1 AS month" +
            "  UNION ALL" +
            "  SELECT month + 1 " +
            "  FROM MonthSequence " +
            "  WHERE month < 12" +
            ")" +
            "SELECT " +
            "  #{year} as year, " +
            "  ms.month, " +
            "  SUM(CASE WHEN a.status = '已签到' THEN a.total_amount ELSE 0 END) as totalAmount " +
            "FROM MonthSequence ms " +
            "LEFT JOIN serviceappointment a ON ms.month = MONTH(a.create_time) " +
            "AND YEAR(a.create_time) = #{year} " +
            "AND a.status = '已签到' " +
            "GROUP BY ms.month " +
            "ORDER BY ms.month")
    List<MonthlyProfit> selectMonthlyProfit(@Param("year") int year);
    //月销售
    //月销售
    @Select("WITH RECURSIVE DateSequence AS (" +
            "  SELECT 1 AS day" +
            "  UNION ALL" +
            "  SELECT day + 1 " +
            "  FROM DateSequence " +
            "  WHERE day < 31" +
            ")" +
            "SELECT " +
            "  #{year} as year, " +
            "  #{month} as month, " +
            "  ds.day, " +
            "  SUM(CASE WHEN a.status = '已签到' THEN a.total_amount ELSE 0 END) as totalAmount " +
            "FROM DateSequence ds " +
            "LEFT JOIN serviceappointment a ON ds.day = DAY(a.create_time) " +
            "AND YEAR(a.create_time) = #{year} " +
            "AND MONTH(a.create_time) = #{month} " +
            "AND a.status = '已签到' " +
            "GROUP BY ds.day " +
            "ORDER BY ds.day")
    List<DailyProfit> selectDailyProfit(@Param("year") int year, @Param("month") int month);
    //appointmentslot_id查询
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "a.total_amount, " +
            "a.discount, " +
            "c.id as service_id, " +
            "c.name, " +
            "b.email, " +
            "b.phone, " +
            "c.original_price, " +
            "c.discount_price, " +
            "a.ordernumber, " +
            "a.appointmentslot_id, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "WHERE a.appointmentslot_id = #{appointmentslot_id}")
    List<Serviceappointment> selectappointmentslot_id(int appointmentslot_id);
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "a.total_amount, " +
            "a.discount, " +
            "c.id as service_id, " +
            "c.name, " +
            "b.email, " +
            "b.phone, " +
            "c.original_price, " +
            "c.discount_price, " +
            "a.ordernumber, " +
            "a.appointmentslot_id, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "where DATE(d.appointment_time) = #{date}")
            List<Serviceappointment> selectOrder(LocalDate date);
    //appointmentslot_id查询
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "a.total_amount, " +
            "a.discount, " +
            "c.id as service_id, " +
            "a.appointmentslot_id, " +
            "c.name, " +
            "b.email, " +
            "b.phone, " +
            "c.original_price, " +
            "c.discount_price, " +
            "a.ordernumber, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "WHERE a.ordernumber like concat('%',#{ordernumber},'%');")
    List<Serviceappointment> selectordernumber(int ordernumber);
    //status查询
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "c.id as service_id, " +
            "c.name, " +
            "a.total_amount, " +
            "a.discount, " +
            "b.email, " +
            "b.phone, " +
            "c.original_price, " +
            "a.appointmentslot_id, " +
            "c.discount_price, " +
            "a.ordernumber, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "WHERE a.status = #{status}")
    List<Serviceappointment> selectstatus(String status);
    //user_id查询
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "a.total_amount, " +
            "a.discount, " +
            "b.id as user_id, " +
            "c.id as service_id, " +
            "c.name, " +
            "b.email, " +
            "b.phone, " +
            "a.ordernumber, " +
            "c.original_price, " +
            "a.appointmentslot_id, " +
            "c.discount_price, " +
            "c.img, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "WHERE b.id = #{user_id}")
    List<Serviceappointment> selectuser_id(int user_id);
    //name查询
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.total_amount, " +
            "a.discount, " +
            "a.status, " +
            "a.tk_status,"+
            "c.id as service_id, " +
            "c.name, " +
            "b.email, " +
            "b.phone, " +
            "a.ordernumber, " +
            "c.original_price, " +
            "c.discount_price, " +
            "a.appointmentslot_id, " +
            "a.create_time, " +
            "a.update_time, " +
            "a.message, " +
            "d.appointment_time, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id " +
            "WHERE c.name = #{name}")
    List<Serviceappointment> selectname(String name);
    //查询所有
    @Select("SELECT " +
            "a.id, " +
            "a.user_id, " +
            "b.username, " +
            "a.status, " +
            "a.tk_status,"+
            "c.id as service_id, " +
            "c.name, " +
            "b.email, " +
            "b.phone, " +
            "c.original_price, " +
            "a.total_amount, " +
            "a.appointmentslot_id, " +
            "a.discount, " +
            "c.discount_price, " +
            "a.create_time, " +
            "a.update_time, " +
            "d.appointment_time, " +
            "a.ordernumber, " +
            "a.message, " +
            "d.deadline " +
            "FROM serviceappointment a " +
            "JOIN user b ON a.user_id = b.id " +
            "JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            "JOIN service c ON d.service_id = c.id ")
    List<Serviceappointment> findAll();
    //添加
    @Insert("insert into serviceappointment(user_id,appointmentslot_id,status,total_amount,discount,ordernumber,message,create_time,update_time) " +
            "values(#{user_id},#{appointmentslot_id},#{status},#{total_amount},#{discount},#{ordernumber},#{message},now(),now());")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int add(Serviceappointment serviceappointment);
    //删除
    @Delete("delete from serviceappointment where id=#{id};")
    int delete(int id);
    //修改数据
    @Update("UPDATE serviceappointment SET user_id=#{user_id},appointmentslot_id=#{appointmentslot_id},status=#{status},update_time=now() where id= #{id};")
    int update(Serviceappointment serviceappointment);
    //修改数据
    @Update("UPDATE serviceappointment SET appointmentslot_id=#{appointmentslot_id},status=#{status},message=#{message},total_amount=#{total_amount},discount=#{discount},update_time=now() where id= #{id};")
    int adminupdate(Serviceappointment serviceappointment);
    //查询所以的过期记录
    @Select("SELECT a.id,a.status,d.deadline FROM serviceappointment a JOIN appointmentslot d ON a.appointmentslot_id = d.id " +
            " WHERE d.deadline < #{now} AND a.status = '未签到'")
    List<Serviceappointment> findExpiredServiceappointment(LocalDateTime now);
    //修改批量修改状态
    @Update("UPDATE serviceappointment SET status=#{status}, update_time=now() WHERE id=#{id}")
    int updatestatus(@Param("id") int id, @Param("status") String status);
    //修改状态
    @Update("UPDATE serviceappointment SET status=#{status}, update_time=now() WHERE id=#{id}")
    int updateServiceappointmentstatus(Serviceappointment serviceappointment);
    @Update("UPDATE serviceappointment SET tk_status=#{tk_status}, update_time=now() WHERE id=#{id}")
    int updatetk_status(Serviceappointment serviceappointment);


    // 管理员批量修改status
    @Update({
            "<script>",
            "UPDATE serviceappointment SET status=#{status}, update_time=now() WHERE id IN",
            "<foreach item='item' collection='array' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    int batchUpdateStatus(@Param("array") int[] ids, @Param("status") String status);
    // 管理员批量删除
    @Delete({
            "<script>",
            "DELETE FROM serviceappointment WHERE id IN",
            "<foreach item='item' collection='array' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    int admindelete(@Param("array") int[] ids);
}
